import os.path
import pandas as pd
from datatoolsobj.dirwalk import DirWalk
from datatoolsobj.excel.excelreader import ExcelReader
from datatoolsobj.excel.excelformat import XlsxWriterFormat
from datatoolsobj.utils import create_dir

import logging

logging.basicConfig(level="INFO")

mylog = logging.getLogger()

class ExcelDirWalk():
    def __init__(self,dir_path,excel_dir_struct_conf_path,from_excel_used_sheets=None,use_exist=True,conf_dir="conf"):
        #super(ExcelDirWalk,self).__init__(dir_path,'excel')
        #生成保存指定目录中所有Excel文件使用配置表信息的Excel文件 
        if excel_dir_struct_conf_path and (-1 != excel_dir_struct_conf_path.find(".xls") or -1 != excel_dir_struct_conf_path.find(".xlsx") ):
            self._dir_struct_conf_path = excel_dir_struct_conf_path
        elif excel_dir_struct_conf_path:
            self._dir_struct_conf_path = excel_dir_struct_conf_path+".xlsx"
        else:
            raise FileNotFoundError("给出的Excel表配置文件路径为空！！")
        #初始化配置文件目录
        if not conf_dir:
            self._conf_dir = "conf"
        else:
            self._conf_dir = conf_dir
        create_dir(conf_dir)

        self.file_No = 1001
        self._dir_path = dir_path

        #此处需要加入判断，如果存在配置文件，则需要根据参数确定是不直接使用
        self._conf_path = os.path.join(self._conf_dir,self._dir_struct_conf_path)
        if os.path.exists(self._conf_path) and use_exist:
            self.inUsedExcelConfFile()
            self._dw = None
            self._new_dir = False
        else:
            #此处为新读取文件结构
            self._dw = DirWalk(dir_path,'excel')
            self._excel_dir_df = self.getExcelDirStruct()
            #此处加入使用参考配置文件进行修改的功能
            if from_excel_used_sheets:
                #构造读取路径
                mylog.debug("从其他项目配置文件[{}]中获取使用表配置信息".format(from_excel_used_sheets))
                to_data = self._excel_dir_df
                from_file_path = os.path.join(self._conf_dir,from_excel_used_sheets+".xlsx")
                from_data = pd.read_excel(from_file_path, header=1)
                for index,row in to_data.iterrows():
                    to_file_path = row['文件路径']
                    to_file_name = row['文件名称']
                    to_sheet_name = row['Sheet表名']
                    find_from_row = from_data.loc[(from_data['文件路径']==to_file_path)&(
                            from_data['文件名称']==to_file_name)&(from_data['Sheet表名']==to_sheet_name)]
                    if not find_from_row.empty:
                        to_data.iloc[index,[4,5,6,7]] = find_from_row.iloc[0,[4,5,6,7]]

            self._new_dir = True

    def getExcelDirDataFrame(self):
        return self._excel_dir_df

    def getExcelDirStruct(self):
        '''获取指定目录下的所有excel文件及其内部的Sheet表。
        '''
        file_path_info_line = []
        if not self._dw:
            self._dw = DirWalk(self._dir_path,'excel')
        dir_struct = self._dw.getDirStruct()
        for index,line in dir_struct.iterrows():
            name = line['文件名称']
            path = line['文件路径']
            if path.find('~$') != -1:
                continue
            er = ExcelReader(path)
            sheet_names = er.getSheetNames()
            if len(sheet_names) == 1:
                file_path_info_line.append([self.file_No,path,name,sheet_names[0],'是'])
                self.file_No += 1
            else:
                first = False
                for sheet_name in er.getSheetNames():
                    if not first:
                        first = True
                        file_path_info_line.append([self.file_No,path,name,sheet_name,'是'])
                    else:
                        file_path_info_line.append([self.file_No,path,name,sheet_name,'否'])
                    self.file_No += 1
        excel_dir_df = pd.DataFrame(file_path_info_line,columns=['文件编号','文件路径','文件名称','Sheet表名','是否使用'])
        excel_dir_df.insert(excel_dir_df.shape[1], '单行列名', '是')
        excel_dir_df.insert(excel_dir_df.shape[1],'年度','')
        excel_dir_df.insert(excel_dir_df.shape[1],"列名起始标识","")
        excel_dir_df.insert(excel_dir_df.shape[1],"列名终止标识","")
        excel_dir_df.insert(excel_dir_df.shape[1],'标记','')
        excel_dir_df.insert(excel_dir_df.shape[1],'备注','')
    
        return excel_dir_df

    def outUsedExcelConfFile(self,excel_dir_struct_conf_path=None):
        '''
        '''
        if excel_dir_struct_conf_path:
            if (-1 != excel_dir_struct_conf_path.find(".xls") or -1 != excel_dir_struct_conf_path.find(".xls") ):
                self._dir_struct_conf_path = excel_dir_struct_conf_path
            else:
                self._dir_struct_conf_path = excel_dir_struct_conf_path+".xlsx"
        
        #self._excel_dir_df = self.getExcelDirStruct()

        path = os.path.join(self._conf_dir,self._dir_struct_conf_path)
        
        colunmn_width = {'文件路径':50,'文件名称':30}
        
        with XlsxWriterFormat(path) as out:
            out.to_excel(self._excel_dir_df,"配置用到的Excel文件",colunmn_width=colunmn_width,title="用到的Excel表配置详情表")

    def inUsedExcelConfFile(self,excel_dir_struct_conf_path=None):
        '''
        '''
        if excel_dir_struct_conf_path:
            if (-1 != excel_dir_struct_conf_path.find(".xls") or -1 != excel_dir_struct_conf_path.find(".xls") ):
                self._dir_struct_conf_path = excel_dir_struct_conf_path
            else:
                self._dir_struct_conf_path = excel_dir_struct_conf_path+".xlsx"

        path = os.path.join(self._conf_dir,self._dir_struct_conf_path)
        self._excel_dir_df = pd.read_excel(path,header=1)
        self._excel_dir_df = self._excel_dir_df.loc[self._excel_dir_df['是否使用'].isin(['是','Y',True,'TRUE','True'])]
        self._new_dir = False
        return self._excel_dir_df

    def isNewDir(self):
        return self._new_dir
    
